﻿-- exec esf_sso.P_GetUserBasicReferenceValues @pUserAlias=N'xxx',@pBasicReferenceTypeID=13

--esf_sso.P_GetUserBasicReferenceValues 'root', 13

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetUserBasicReferenceValues')
	BEGIN
		DROP  Procedure  esf_sso.P_GetUserBasicReferenceValues
	END

GO

CREATE Procedure esf_sso.P_GetUserBasicReferenceValues
	@pUserAlias					VARCHAR (20)
	,@pBasicReferenceTypeID		INT
	,@pBasicReferenceCode		VARCHAR (60) = NULL
	,@pOnlyByIsEnabled			BIT = NULL
AS

	-- Si hay valores registrados para el usuario por tipo se extraen
	IF (EXISTS (SELECT TOP 1 1 
		FROM 
			esf_sso.User_BasicReference_Values BUR
		LEFT JOIN
			esf_sso.[User] U ON	U.PersonID = BUR.UserPersonID
		LEFT JOIN
			esf_sso.BasicReference BR ON BR.ID = BUR.BasicReferenceID
		WHERE
			U.Alias = @pUserAlias
			AND (@pBasicReferenceCode IS NULL OR BR.Code = @pBasicReferenceCode) ) )
	BEGIN
	
		SELECT 
			DISTINCT 
			BR.ID
			,BR.Code
			,BR.Name
			,COALESCE (
				(SELECT 
					BUR.Value 
				FROM 
					esf_sso.User_BasicReference_Values BUR 
				INNER JOIN
					esf_sso.[User] U ON	
						U.PersonID = BUR.UserPersonID
						AND U.Alias = @pUserAlias
				WHERE 
					BR.ID = BUR.BasicReferenceID), BR.Extra_I)	Value
		FROM
			esf_sso.BasicReference BR
		
		WHERE
			BR.BasicReferenceTypeID = @pBasicReferenceTypeID
			AND (@pOnlyByIsEnabled IS NULL OR BR.IsEnabled = @pOnlyByIsEnabled)
			AND (@pBasicReferenceCode IS NULL OR BR.Code = @pBasicReferenceCode) 
				
	END
	ELSE
	BEGIN
	
		SELECT 
			BR.ID
			,BR.Code
			,BR.Name
			,BR.Extra_I	Value
		FROM
			esf_sso.BasicReference BR
		WHERE
			BR.BasicReferenceTypeID = @pBasicReferenceTypeID
			AND (@pOnlyByIsEnabled IS NULL OR BR.IsEnabled = @pOnlyByIsEnabled)
			AND (@pBasicReferenceCode IS NULL OR BR.Code = @pBasicReferenceCode)
	
	END

GO